package com.elitel.frame.business.service.impl;

import com.alibaba.fastjson.JSON;
import com.elitel.common.utils.XCommonUtils;
import com.elitel.frame.base.component.RedisCacheCfgMaster;
import com.elitel.frame.base.component.RedisCacheComponent;
import com.elitel.frame.base.component.RedisCacheDataBase;
import com.elitel.frame.base.service.*;
import com.elitel.frame.business.dao.ext.SingleTableServerMapper;
import com.elitel.frame.business.service.DataBaseMetadataService;
import com.elitel.frame.business.service.TableFieldCommService;
import com.elitel.frame.main.entity.*;
import com.elitel.frame.main.entity.ext.*;
import com.elitel.frame.main.entity.vo.ConQueryVo;
import freemarker.cache.StringTemplateLoader;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <pre>@ProjectName：frame-business-service
 * @ClassName：TableFieldCommService
 * @Description: TODO(查询表格显示字段和查询字段公共服务接口实现类)
 * @author guoyanfei
 * @date：2017/11/2
 * @version </pre>
 */
@Service
public class TableFieldCommServiceImpl implements TableFieldCommService {
	@Autowired
	private RedisCacheComponent redisCacheComponent;
	@Autowired
	private RedisCacheDataBase redisCacheDataBase;
	@Autowired
	private BaseFieldconfigService baseFieldconfigService;
	@Autowired
	private RedisCacheCfgMaster redisCacheCfgMaster;
	@Autowired
	private BaseFieldenumService baseFieldenumService;
	@Autowired
	private SingleTableServerMapper singleTableServerMapper;
	@Autowired
	private BaseRepparmeterService baseRepparmeterService;
	@Autowired
	private BaseQueryfieldService baseQueryfieldService;
	@Autowired
	private DataBaseMetadataService dataBaseMetadataService;
	@Autowired
	private BaseFieldaddonsService baseFieldaddonsService;

	/**
	 * show 查询服务显示列
	 * @param cfgDb(数据源)
	 * @param serviceID(服务ID)
	 * @param serviceType(服务类型,1单表查询服务,2主从表查询服务)
	 * @param masterDetailId(主从表服务-从表信息ID)
	 * @return 显示列集合
	 * @author guoyanfei
	 * @date 2017/11/17
	 */
	@Override
	public List<Map<String, String>> getServiceShowField(CfgDb cfgDb, String serviceID, String serviceType,String masterDetailId) {
		List<Map<String, String>> resultMapList = new ArrayList<Map<String, String>>();

		List<BaseShowfieldExt> baseShowfieldExts = redisCacheDataBase.selectBaseShowfieldRedisCache(serviceID, serviceType,masterDetailId);
		if(XCommonUtils.isEmpty(baseShowfieldExts))
			return resultMapList;

		for (BaseShowfieldExt baseShowfieldExt : baseShowfieldExts) {
			Map<String, String> map = new LinkedHashMap<String,String>();
			String fieldname = XCommonUtils.isEmpty(baseShowfieldExt.getCustomAlias()) ? baseShowfieldExt.getFieldname() : baseShowfieldExt.getCustomAlias();
			String fieldnameCn = XCommonUtils.isEmpty(baseShowfieldExt.getCustomFieldNameAlias()) ? baseShowfieldExt.getFieldnameCn() : baseShowfieldExt.getCustomFieldNameAlias();
			fieldnameCn = StringUtils.isEmpty(fieldnameCn) ? fieldname : fieldnameCn;
			map.put("fieldname",fieldname);
			map.put("fieldnamecn",fieldnameCn);
			map.put("fieldtype",baseShowfieldExt.getFieldtype());
			resultMapList.add(map);
		}

		return resultMapList;
	}


	// here directly return BaseShowFieldExt object
	public List<BaseShowfieldExt> getServiceShowFieldForView(CfgDb cfgDb, String serviceID, String serviceType,String masterDetailId) {
		return redisCacheDataBase.selectBaseShowfieldRedisCache(serviceID, serviceType,masterDetailId);
	}




	/**
	 * <pre>querySingleTBList(单表显示字段)
	 * @author guoyanfei
	 * @param cfgDb(数据源)
	 * @param dtConfigId(表标识)
	 * @return</pre>
	 */
	@Override
	public List<Map<String, String>> getSingleTableShowField(CfgDb cfgDb, String dtConfigId) {
		List<Map<String,String>> result = new ArrayList<>();
		List<CfgField> cfgFields = redisCacheComponent.cfgFieldRedisCache(dtConfigId);
		if(cfgFields != null){
			for (CfgField cfgField : cfgFields) {
				Map<String, String> map = new LinkedHashMap<String,String>();
				map.put("fieldname",cfgField.getDfName());
				map.put("fieldnamecn",cfgField.getDfNameCn());
				map.put("fieldtype",cfgField.getDtType());
				result.add(map);
			}
		}
		return result;
	}


	/**
	 * <pre>querySingleTBList(视图显示字段)
	 * @author guoyanfei
	 * @param cfgDb(数据源)
	 * @param dtConfigId(表标识)
	 * @return</pre>
	 */
	@Override
	public List<Map<String, String>> getViewShowField(CfgDb cfgDb, String dtConfigId) {
		List<Map<String,String>> result = new ArrayList<>();
		List<BaseFieldaddonsExt>baseFieldaddonsExts = redisCacheDataBase.selectExtendFieldaddonsListRedisCache(dtConfigId);
		if(baseFieldaddonsExts.size() > 0){
			for (BaseFieldaddonsExt baseFieldaddonsExt : baseFieldaddonsExts) {
				Map<String, String> map = new LinkedHashMap<String,String>();
				if(baseFieldaddonsExt.getIsmodfname().toString().equals("1")){
					map.put("fieldname",baseFieldaddonsExt.getModifieldname());
				}else{
					map.put("fieldname",baseFieldaddonsExt.getFieldname());
				}
				if(baseFieldaddonsExt.getIsmodfcaption().toString().equals("1")){
					map.put("fieldnamecn", baseFieldaddonsExt.getModifieldcaption());
				}else{
					map.put("fieldnamecn", baseFieldaddonsExt.getFieldnameCn());
				}

				map.put("fieldtype", baseFieldaddonsExt.getFieldtype());
				result.add(map);
			}
		}
		return result;
	}


	/**
	 * <pre>querySingleTBList(获取服务表格查询字段)
	 * @author guoyanfei
	 * @param cfgDb(数据源)
	 * @param serviceID(服务ID)
	 * @param serviceType(服务类型)
	 * @param grade(资源树层级)
	 * @return</pre>
	 */
	@Override
	public List<Map<String, String>> getTableQueryField(CfgDb cfgDb, String serviceID, Integer serviceType,String grade) {
		List<Map<String,String>> result = new ArrayList<>();
		String dtConfigId = "";
		List<BaseQueryfieldExt> baseQueryfields = redisCacheDataBase.selectBaseQueryfieldRedisCache(serviceID, serviceType.toString(),grade);
		if(baseQueryfields != null && baseQueryfields.size() > 0){
			for (BaseQueryfieldExt baseQueryfieldExt : baseQueryfields) {
				Map<String, String> map = new LinkedHashMap<String,String>();
				map.put("fieldname",baseQueryfieldExt.getParametercode());
				// 如果从数据库或者缓存中拿取到的字段中文名称是空的 就获取filedname
				map.put("fieldnamecn", StringUtils.isEmpty(baseQueryfieldExt.getFieldnameCn()) ? baseQueryfieldExt.getParametercode() : baseQueryfieldExt.getFieldnameCn());
				map.put("fieldtype",baseQueryfieldExt.getFieldtype());
				map.put("showtype",baseQueryfieldExt.getShowtype().toString());
				map.put("enumvalue",baseQueryfieldExt.getEnumvalue());
				result.add(map);
			}
		}


		return result;
	}


	/**
	 * <pre>getTablenameTxt(通过表标识获取表名称)
	 * @author guoyanfei
	 * @param dtConfigId(表标识)
	 * @param cfgDb(数据源)
	 * @return</pre>
	 */
	@Override
	public String getTablenameTxt(CfgDb cfgDb,String dtConfigId) {
		StringBuffer queryTableExpression = new StringBuffer();
		CfgTable cfgTable = redisCacheComponent.cfgTableRedisCache(dtConfigId,cfgDb.getDbKey());
		if(cfgTable == null)
			return "";

		if(XCommonUtils.isOriginSingleTableType(cfgTable.getDtTabletype()))//数据表
			queryTableExpression.append(" "+cfgTable.getDtName()+" AA");

		if(XCommonUtils.isViewTableType(cfgTable.getDtTabletype())){//视图

			CfgTable cfgOriginSingleTable = redisCacheComponent.cfgTableRedisCache(cfgTable.getDtName(),cfgDb.getDbKey());
			List<BaseFieldconfigExt> baseFieldConfigExtList = baseFieldconfigService.getAllFieldConfig(dtConfigId);
			//设置主表
			queryTableExpression.append(" "+cfgOriginSingleTable.getDtName()+" ");
			//设置关联表
			if(XCommonUtils.isEmpty(baseFieldConfigExtList))
				return queryTableExpression.toString();

			Map<String, List<BaseFieldconfigExt>> baseFieldconfigByGroup = baseFieldConfigExtList.stream().collect(Collectors.groupingBy(o -> o.getTableName()));

			baseFieldconfigByGroup.keySet().forEach(tableNameKey -> {
				List<BaseFieldconfigExt> baseFieldconfigExts = baseFieldconfigByGroup.get(tableNameKey);
				// 关联关系
				queryTableExpression.append(XCommonUtils.concatRelativeOfBetweenTable(baseFieldconfigExts.get(0).getRelationType()));
				queryTableExpression.append(tableNameKey + " on ");

				String condition = "";
				for ( BaseFieldconfigExt bfconfigext: baseFieldconfigExts) {
					if(!StringUtils.equals(condition, "")){
						condition += " and ";
					}
					condition += bfconfigext.getTableName() + "." + bfconfigext.getForeignCode()+ " = " + cfgOriginSingleTable.getDtName() + "." + bfconfigext.getFieldid();
				}
				queryTableExpression.append(condition);
			});
		}

		return queryTableExpression.toString();
	}

	/**
	 * <pre>getTablenameTxt(通过表标识获取配置字段名称)
	 * @author guoyanfei
	 * @param dtConfigId(表标识)
	 * @param cfgDb(数据源)
	 * @return</pre>
	 */
	@Override
	public String getFieldnameTxt(CfgDb cfgDb, String dtConfigId) {
		StringBuffer fieldtxt = new StringBuffer();
		CfgTable cfgTable = redisCacheComponent.cfgTableRedisCache(dtConfigId,cfgDb.getDbKey());
		if(cfgTable != null){
			if(cfgTable.getDtTabletype() == 1){//数据表
				List<CfgField> cfgFields = redisCacheComponent.cfgFieldRedisCache(dtConfigId);
				if(cfgFields != null){
					for (CfgField cfgField : cfgFields) {
						fieldtxt.append(dtConfigId+"."+cfgField.getDfName()+",");
					}
				}
				List<BaseFieldenum> baseFieldenums = baseFieldenumService.getBaseFieldenumList(dtConfigId);
				//设置字段枚举
				if(baseFieldenums != null && baseFieldenums.size() > 0){
					for (BaseFieldenum baseFieldenum : baseFieldenums) {

						if(baseFieldenum.getEnumvalue() != null && !baseFieldenum.getEnumvalue().equals("")){
							fieldtxt.append("(CASE "+baseFieldenum.getFieldname()+" ");
							String[] enums = baseFieldenum.getEnumvalue().toString().split(",");
							for (String str : enums) {
								String[] keyval = str.split(":");
								if(keyval.length > 1){
									fieldtxt.append(" WHEN '"+keyval[0]+"' THEN '"+keyval[1]+"' ");
								}
							}
							fieldtxt.append(" else '' end)"+baseFieldenum.getEnumcode()+",");
						}

					}
				}
			}else if(cfgTable.getDtTabletype() == 2){//视图
				CfgTable cfgView = redisCacheComponent.cfgTableRedisCache(cfgTable.getDtName(),cfgDb.getDbKey());
				if(cfgView != null){
					List<BaseFieldaddonsExt> fieldaddonslist = redisCacheDataBase.selectExtendFieldaddonsListRedisCache(dtConfigId);
					if(fieldaddonslist !=null){
						for (BaseFieldaddonsExt baseFieldaddonsExt : fieldaddonslist) {
							//查询字段对应表别名
							String tablealterName = baseFieldaddonsExt.getAltername();

							String strField="";
							//设置列枚举
							if(baseFieldaddonsExt.getIsenum() != null && baseFieldaddonsExt.getIsenum().equals("1")){
								BaseFieldenum baseFieldenum = baseFieldenumService.getBaseFieldenumByguid(baseFieldaddonsExt.getFieldid());
								strField+="(CASE "+tablealterName+"."+baseFieldenum.getFieldname();
								String[] enums = baseFieldenum.getEnumvalue().toString().split(",");
								for (String str : enums) {
									String[] keyvalue = str.split(":");
									strField+=" WHEN '"+keyvalue[0]+"' THEN '"+keyvalue[1]+"' ";
								}
								strField+=" END)";

								if(baseFieldaddonsExt.getIsmodfname() != null && baseFieldaddonsExt.getIsmodfname().toString().equals("1")){//设置别名
									fieldtxt.append(strField+" as "+baseFieldaddonsExt.getModifieldname()+",");
								}else{
									fieldtxt.append(strField+baseFieldaddonsExt.getFieldname()+",");
								}
							}else{
								strField=tablealterName+"."+baseFieldaddonsExt.getFieldname();
								if(baseFieldaddonsExt.getIsmodfname().toString().equals("1")){//设置别名
									fieldtxt.append(strField+" as "+baseFieldaddonsExt.getModifieldname()+",");
								}else{
									fieldtxt.append(strField+",");
								}
							}

						}
					}
				}

			}
		}
		String fields = fieldtxt.toString();
		fields = fields.substring(0,fields.length()-1);
		return fields;
	}


	/**
	 * @description 通过服务ID和服务类型查询改服务的显示字段拼接串
	 * @param cfgDb(数据源)
	 * @param serviceID(服务ID)
	 * @param serviceType(服务类型:1单表服务,2主从表服务)
	 * @param dtConfigId(表标识)
	 * @param masterDetailId(主从表服务-从表信息ID)
	 * @return 字段拼接串
	 * @author guoyanfei
	 * @date 2017/11/17
	 */
	@Override
	public String getFieldnameByserviceID(CfgDb cfgDb, String serviceID, String serviceType,String dtConfigId,String masterDetailId) {
		StringBuffer queryFields = null;
		List<BaseShowfieldExt> baseShowfieldExts = redisCacheDataBase.selectBaseShowfieldRedisCache(serviceID, serviceType,masterDetailId);
		CfgTable cfgTable = redisCacheComponent.cfgTableRedisCache(dtConfigId,cfgDb.getDbKey());
		if(cfgTable == null)
			return "";

		// 判断数据库类型,如果是MongoDB 不能拼接as别名  特殊置空
		if ("MongoDB".equals(cfgDb.getDbVersion())) {
			baseShowfieldExts.stream().forEach(baseShowfieldExt -> {
				baseShowfieldExt.setCustomAlias(null);
				baseShowfieldExt.setCustomFieldNameAlias(null);
			});
		}

		if(XCommonUtils.isOriginSingleTableType(cfgTable.getDtTabletype()))//数据表
			queryFields = handleQueryFieldForSingleTable(dtConfigId, baseShowfieldExts);

		if(XCommonUtils.isViewTableType(cfgTable.getDtTabletype()))//视图
			queryFields = handleQueryFieldsForView(dtConfigId, baseShowfieldExts);


		String fields = queryFields.toString();
		return XCommonUtils.isEmpty(fields) ? "" : fields.substring(0,fields.length()-1);
	}

	// here is handle (judge is originSingleTable or view)
	@Override
	public String handleWhereConditionByTableType(CfgDb cfgDb, String primaryKeyValueArray, CfgTable cfgTable){
		if(XCommonUtils.isEmpty(primaryKeyValueArray) || cfgTable == null)
			return "";

		String[] primaryKeyArray = cfgTable.getDtPrimarykey().split(",");
		String[] valueArray = primaryKeyValueArray.split(",");
		String condition = null;

		String alterName = " AA.";

		if(XCommonUtils.isViewTableType(cfgTable.getDtTabletype())){
			List<BaseFieldaddons> baseFieldaddonsList = baseFieldaddonsService.selectFieldaddonsBydtconfigid(cfgTable.getDtConfigId());
			if(baseFieldaddonsList != null)
				alterName = baseFieldaddonsList.get(0).getAltername()+".";
		}

		for (Integer i=0;i<primaryKeyArray.length;i++){
			if(condition == null){
				condition = alterName + primaryKeyArray[i] + "='" + valueArray[i] + "'";
				continue;
			}
			condition += " and  " + alterName + primaryKeyArray[i] + "='" + valueArray[i] + "'";
		}

		return " where " + condition;
	}


	private StringBuffer handleQueryFieldsForView(String dtConfigId, List<BaseShowfieldExt> baseShowFieldExtList) {
		StringBuffer queryFields = new StringBuffer();
		List<BaseFieldaddonsExt> baseFieldAddonsList =  baseFieldaddonsService.selectExtendFieldaddonsList(dtConfigId);

		if(XCommonUtils.isEmpty(baseFieldAddonsList))
			return queryFields;


		Map<String, List<BaseFieldaddonsExt>> baseFieldAddonsListMap = baseFieldAddonsList.stream().collect(Collectors.groupingBy(BaseFieldaddonsExt :: getGuid));

		for(BaseShowfieldExt baseShowFieldExt : baseShowFieldExtList){
			List<BaseFieldaddonsExt> baseFieldAddonsListByGuid = baseFieldAddonsListMap.get(baseShowFieldExt.getFieldaddonsguid());
			if(XCommonUtils.isEmpty(baseFieldAddonsListByGuid))
				continue;// it's should not happen

			BaseFieldaddonsExt baseFieldAddonsExt = baseFieldAddonsListByGuid.get(0);
			//设置列枚举
			if(XCommonUtils.isFieldEnumType(baseFieldAddonsExt.getIsenum()))
				handleEnumField(queryFields, baseFieldAddonsExt, baseFieldAddonsExt.getAltername(), baseShowFieldExt);
			else
				handleNormalField(queryFields, baseFieldAddonsExt, baseFieldAddonsExt.getAltername(), baseShowFieldExt);

		}

		return queryFields;
	}


	// here is for columnName , but I only need to set alias ,it's nice ,but ok
    private void handleNormalField(StringBuffer queryFields, BaseFieldaddonsExt baseFieldAddonsExt, String tablealterName, BaseShowfieldExt baseShowFieldExt) {

		String strField = tablealterName + "." + baseFieldAddonsExt.getFieldname();

        if(XCommonUtils.isModifyNameForField("" +baseFieldAddonsExt.getIsmodfname()))
        	queryFields.append(strField+" as "+baseFieldAddonsExt.getModifieldname()+",");
        else {
			String columnAlias = XCommonUtils.isEmpty(baseShowFieldExt.getCustomAlias()) ? tablealterName + "_" + baseFieldAddonsExt.getFieldname() : baseShowFieldExt.getCustomAlias();
            queryFields.append(strField+" as "+ columnAlias + " ,");
        }
    }


	// here is can sync single table handle , only need to handle baseFieldEnum object
    private void handleEnumField(StringBuffer queryFields, BaseFieldaddonsExt baseFieldAddonsExt, String tablealterName, BaseShowfieldExt baseShowFieldExt) {
		String strField = "";
        BaseFieldenum baseFieldenum = baseFieldenumService.getBaseFieldenumByguid(baseFieldAddonsExt.getFieldid());
        if(XCommonUtils.isJSONFieldEnumType(baseFieldenum.getEnumtype())){
        	//自定义JSON
        	strField+="(CASE "+tablealterName+"."+baseFieldenum.getFieldname();
        	Map<String,String> enumMap = (Map<String,String>) JSON.parse(baseFieldenum.getEnumvalue().toString());
        	for (String key:enumMap.keySet()){
        		String value = enumMap.get(key).toString();
        		strField+=" WHEN '"+key+"' THEN '"+value+"' ";
        	}
        	strField+=" END)";
        }else if(XCommonUtils.isSQLFieldEnumType(baseFieldenum.getEnumtype())){
        	//SQL语句
        	strField += "(select enum_value from(";
        	strField += baseFieldenum.getEnumvalue();
        	strField += ") enum where enum.enum_key="+tablealterName+"."+baseFieldenum.getFieldname()+")";
        }

        if(XCommonUtils.isModifyNameForField("" +baseFieldAddonsExt.getIsmodfname())){//设置别名
        	queryFields.append(strField + " as " + baseFieldAddonsExt.getModifieldname()+",");
        }else{
        	String columnAlias =XCommonUtils.isEmpty(baseShowFieldExt.getCustomAlias()) ? baseFieldAddonsExt.getFieldname() : baseShowFieldExt.getCustomAlias();
            queryFields.append(strField + " as " +columnAlias+",");
        }
    }


    private StringBuffer handleQueryFieldForSingleTable(String dtConfigId,  List<BaseShowfieldExt> baseShowfieldExts) {
		StringBuffer queryFields = new StringBuffer();

        List<BaseFieldenum> baseFieldEnums = baseFieldenumService.getBaseFieldenumList(dtConfigId);
        List<CfgField> cfgFields = redisCacheComponent.cfgFieldRedisCache(dtConfigId);

        if(XCommonUtils.isEmpty(cfgFields) || XCommonUtils.isEmpty(baseShowfieldExts))
			return queryFields;


		Map<String, List<BaseShowfieldExt>> baseShowFieldExtListMap = baseShowfieldExts.stream().collect(Collectors.groupingBy(BaseShowfieldExt :: getFieldaddonsguid));
		for(CfgField cfgField : cfgFields){
			List<BaseShowfieldExt> baseShowFieldList = baseShowFieldExtListMap.get(cfgField.getGuid());
			if(XCommonUtils.isEmpty(baseShowFieldList))
				continue;

			BaseShowfieldExt baseShowFieldExt = baseShowFieldList.get(0);
			String columnName = "AA." + cfgField.getDfName();
			String columnAliasName = columnName + getColumnAliasName(baseShowFieldExt);

            queryFields.append(columnAliasName+",");
		}

		//设置字段枚举
		if(XCommonUtils.isEmpty(baseFieldEnums))
			return queryFields;

		for (BaseFieldenum baseFieldenum : baseFieldEnums) {
			List<BaseShowfieldExt> baseShowFieldExtList = baseShowFieldExtListMap.get(baseFieldenum.getGuid());
			if(XCommonUtils.isEmpty(baseShowFieldExtList))
				continue;

			BaseShowfieldExt baseShowFieldExt = baseShowFieldExtList.get(0);
			String columnAliasName = XCommonUtils.isEmpty(baseShowFieldExt.getCustomAlias())? baseFieldenum.getEnumcode() : baseShowFieldExt.getCustomAlias();
            if(XCommonUtils.isJSONFieldEnumType(baseFieldenum.getEnumtype())){
				//自定义JSON
				queryFields.append("(CASE "+baseFieldenum.getFieldname()+" ");
				Map<String,String> enumMap = (Map<String,String>)JSON.parse(baseFieldenum.getEnumvalue().toString());
				for (String key:enumMap.keySet()){
					String value = enumMap.get(key).toString();
					queryFields.append(" WHEN '"+key+"' THEN '"+value+"' ");
				}
				queryFields.append(" else '' end)"+columnAliasName+",");

			}else if(XCommonUtils.isSQLFieldEnumType(baseFieldenum.getEnumtype())){
				//SQL语句
				queryFields.append("(select enum_value from(");
				queryFields.append(baseFieldenum.getEnumvalue());
				queryFields.append(") enum where enum.enum_key="+baseFieldenum.getFieldname()+") "+columnAliasName+",");
			}
		}

		return queryFields;
    }


    private String getColumnAliasName(BaseShowfieldExt baseShowFieldExt) {
        return XCommonUtils.isEmpty(baseShowFieldExt.getCustomAlias()) ? "" : " as " + baseShowFieldExt.getCustomAlias();
    }




	/**
	 * 
	 * @Title: loadWhereSql 
	 * @Description: TODO(根据不同的数据库类型返回不同的条件语句) 
	 * @author guoyanfei
	 * @return String
	 * @throws
	 */
	@Override
	public String loadWhereSql(CfgDb cfgDb, List<ConQueryVo> list) {
		String dbType= cfgDb.getDbVersion();
		StringBuffer str = new StringBuffer("");
		switch (dbType) {
		case "ORACLE":
			for (int i = 0; i < list.size(); i++) {
				if(i != 0) {
					str.append(" and ");
				}
				String field= list.get(i).getField().replace("__",".");
				switch (list.get(i).getType()) {
				case "int":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "string":
					if (list.get(i).getCon().equals("like")) {
						str.append(" "+field);
						str.append(" "+list.get(i).getCon()+" ");
						str.append("'%" + list.get(i).getValue() + "%'");
					} else {
						str.append(" "+field);
						str.append(list.get(i).getCon());
						str.append("'" + list.get(i).getValue() + "'");
					}
					break;
				case "year":
					str.append(" to_CHAR("+field + ",'yyyy')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "month":
					str.append(" to_CHAR("+field + ",'MM')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "day":
					str.append(" to_CHAR("+field + ",'dd')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "ymonth":
					str.append(" to_CHAR("+field + ",'yyyy-MM')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "date":
					str.append(" to_CHAR("+field + ",'yyyy-MM-dd')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "datehour":
					str.append(" to_CHAR("+field + ",'yyyy-MM-dd hh24')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "datehmin":
					str.append(" to_CHAR("+field + ",'yyyy-MM-dd hh24:mi')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "datetime":
					str.append(" to_CHAR("+field + ",'yyyy-MM-dd hh24:mi:ss')");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				}
				
			}
			break;

		case "MYSQL":
			for (int i = 0; i < list.size(); i++) {
				if(i != 0) {
					str.append(" and ");
				}
				String field= list.get(i).getField().replace("__",".");
				switch (list.get(i).getType()) {
				case "int":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "string":
					if (list.get(i).getCon().equals("like")) {
						str.append(" "+field);
						str.append(" "+list.get(i).getCon()+" ");
						str.append("'%" + list.get(i).getValue() + "%'");
					} else {
						str.append(" "+field);
						str.append(list.get(i).getCon());
						str.append("'" + list.get(i).getValue() + "'");
					}
					break;
				case "year":
					str.append(" year("+field + ")");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "month":
					str.append(" month("+field + ")");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "day":
					str.append(" DAY("+field + ")");
					str.append(list.get(i).getCon());
					str.append("'" + list.get(i).getValue() + "'");
					break;
				case "ymonth":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m')");
					break;
				case "date":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d')");
					break;
				case "datehour":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d %H')");
					break;
				case "datehmin":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d %H:%i')");
					break;
				case "datetime":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(" str_to_date('" + list.get(i).getValue() + "','%Y-%m-%d %H:%i:%S')");
					break;
				}
				
			}
			break;
		case "SQLSERVER":
			for (int i = 0; i < list.size(); i++) {
				if(i != 0) {
					str.append(" and ");
				}
				String field= list.get(i).getField().replace("__",".");
				switch (list.get(i).getType()) {
				case "int":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "string":
					if (list.get(i).getCon().equals("like")) {
						str.append(" "+field);
						str.append(" "+list.get(i).getCon()+" ");
						str.append("'%" + list.get(i).getValue() + "%'");
					} else {
						str.append(" "+field);
						str.append(list.get(i).getCon());
						str.append("'" + list.get(i).getValue() + "'");
					}
					break;
				case "year":
					str.append(" year("+field + ")");
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "month":
					str.append(" month("+field + ")");
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "day":
					str.append(" day("+field+ ")");
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "ymonth":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "date":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "datehour":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "datehmin":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				case "datetime":
					str.append(" "+field);
					str.append(list.get(i).getCon());
					str.append(list.get(i).getValue());
					break;
				}
				
			}
			break;
		}
		return str.toString();
	}


	@Override
	public String getQuerysqlByserviceId(CfgDb cfgDb,String serviceID, String serviceType, String grade) {
		String sqltxt = "";
		BaseQuerysql baseQuerysql = redisCacheDataBase.selectBaseQuerysqlRedisCache(serviceID, serviceType, grade);
		if(baseQuerysql != null){
			sqltxt = baseQuerysql.getSqlcontent();
		}
		return sqltxt;
	}

	// here is write how to get queryField , existed
	public List<BaseQueryfieldExt> getQueryFieldObjectByServiceIdAndServiceType(CfgDb cfgDb, String serviceId, String serviceType){
		// here is find BaseQueryfieldExt object
		return baseQueryfieldService.getQueryFieldListByServiceId(serviceId, serviceType, "", null);
	}


	@Override
	public String processSqlByFreemarker(CfgDb cfgDb,String sql, Map condition) {
		String str="";
		Configuration cfg = new Configuration();  
        StringTemplateLoader stringLoader = new StringTemplateLoader();
		if(XCommonUtils.isEmpty(sql))
			return str;

		String sqlWithoutHandlePlaceHolderValue = sql.replaceAll(XCommonUtils.OpenTokenInFreemarker, XCommonUtils.OpenToken).replaceAll(XCommonUtils.CloseTokenInFreemarker, XCommonUtils.CloseToken);

        try {
			stringLoader.putTemplate("myTemplate",sqlWithoutHandlePlaceHolderValue);
			cfg.setTemplateLoader(stringLoader);
			Template template = cfg.getTemplate("myTemplate","utf-8");
			StringWriter writer = new StringWriter();
			template.process(condition, writer);
			str = writer.toString();

        } catch (IOException e) { 
            e.printStackTrace();  
        }catch (TemplateException e) {  
            e.printStackTrace();  
        }
		return str;
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Object> validateFieldValue(CfgDb cfgDb,CfgEdit cfgEdit,String conQuery,String operationType) {
		Map<String, Object> mapResult = new HashMap<String,Object>();
		mapResult.put("Ispass","true");
		mapResult.put("Message","");
		List<BaseEditconfigExt> baseEditconfigExts = redisCacheDataBase.selectBaseEditconfigRedisCache(cfgEdit.getGuid());
		if(XCommonUtils.isEmpty(baseEditconfigExts)){
			mapResult.put("Ispass","false");
			mapResult.put("Message","没有配置校验列");
			return mapResult;
		}

		Map<String,Object> maps = (Map<String,Object>)JSON.parse(conQuery);
		for (BaseEditconfigExt baseEditconfigExt : baseEditconfigExts) {
			String fieldNameKey = baseEditconfigExt.getDfname();
			Object fieldValue = maps.get(fieldNameKey);
			if(fieldValue == null)
				continue;

			Map<String,Object> mapvalidate = validateResult(cfgDb, cfgEdit, baseEditconfigExt.getValidatevalue(), fieldNameKey, fieldValue.toString());
			if(mapvalidate.get("Ispass").equals("false")){
				mapResult.put("Ispass","false");
				mapResult.put("Message",mapResult.get("Message")+";"+mapvalidate.get("Message"));
			}
		}

		return mapResult;
	}

	@SuppressWarnings({ "unchecked"})
	@Override
	public Map<String, Object> validateResult(CfgDb cfgDb,CfgEdit cfgEdit,String validateValue, String fieldCode,String fieldValue) {
		Map<String, Object> maps = new HashMap<String,Object>();
		String validateCode = "";
		String validateJson = "";
		Map<String, Object> mapvalidate = (Map<String, Object>)JSON.parse(validateValue);
		/*JSONObject jsonObject = JSONObject.parseObject(validateValue);
		Map<String, Object> mapvalidate = new HashMap<String, Object>();
		mapvalidate.putAll(jsonObject);*/
		if(mapvalidate != null && mapvalidate.size() > 0){
			for (Map.Entry<String, Object> me : mapvalidate.entrySet()) {
				validateCode = me.getKey();
				//validateJson = JSON.toJSONString( me.getValue().toString());
				validateJson = me.getValue().toString();
			}
		}
		switch (validateCode) {
		case "primarykey":
			//唯一性校验
			if(fieldValue !=null && !fieldValue.equals("")){
				CfgTable cfgTable = redisCacheComponent.cfgTableRedisCache(cfgEdit.getDtConfigId(), cfgEdit.getDbKey());
				if(cfgTable != null){
					String sqltxt = "select count(*) from "+cfgTable.getDtName()+" where "+fieldCode+" = '"+fieldValue+"'";
					Integer count = selectMSSqlCount(cfgDb,sqltxt);
					if(count > 0){
						maps.put("Ispass","false");
						maps.put("Message","字段:"+fieldCode+",违反校验规则,唯一性校验");
					}else{
						maps.put("Ispass", "true");
					}
				}else {
					maps.put("Ispass","false");
					maps.put("Message","字段:"+fieldCode+",违反校验规则,唯一性校验");
				}
			}else{
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,唯一性校验");
			}
			
			break;
		case "guid":
			//自动生成Guid
			maps.put("Ispass", "true");
			break;
		case "notnull":
			//非空校验
			if(fieldValue != null && !fieldValue.equals("")){
				maps.put("Ispass", "true");
			}else {
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,非空校验");
			}
			break;
		case "phone":
			//手机号校验
			if(fieldValue.matches("1\\d{10}")){
				maps.put("Ispass", "true");
			}else{
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,手机号校验");
			}
			break;
		case "telephone":
			//电话号码校验
			if(fieldValue.matches("0\\d{2,3}[-]?\\d{7,8}|0\\d{2,3}\\s?\\d{7,8}|13[0-9]\\d{8}|15[1089]\\d{8}")){
				maps.put("Ispass", "true");
			}else{
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,电话号码校验");
			}
			break;
		case "email":
			//邮箱验证
			if(fieldValue.matches("\\w+(\\.\\w)*@\\w+(\\.\\w{2,3}){1,3}")){
				maps.put("Ispass", "true");
			}else{
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,邮箱校验");
			}
			break;
		case "int":
			//整数验证(包括范围)
			if(fieldValue.matches("^-?\\d+$")){
				Map<String,Integer> intvalue = (Map<String, Integer>)JSON.parse(validateJson);
				try {
					boolean result = true;
					Integer minvalue = intvalue.get("minvalue");
					Integer maxvalue = intvalue.get("maxvalue");
					if(minvalue != null && !minvalue.equals("")){
						if(Integer.parseInt(fieldValue) < minvalue){
							maps.put("Ispass","false");
							maps.put("Message","字段:"+fieldCode+",违反校验规则,整数校验(包括范围),值小于最小范围!");
							result = false;
						}
					}
					if(maxvalue != null && !maxvalue.equals("")){
						if(Integer.parseInt(fieldValue) > maxvalue){
							maps.put("Ispass","false");
							maps.put("Message",maps.get("Message")+";字段:"+fieldCode+",违反校验规则,整数校验(包括范围),值大于最大范围!");
							result = false;
						}
					}
					if(result){
						maps.put("Ispass", "true");
					}
				} catch (Exception e) {
					maps.put("Ispass","false");
					maps.put("Message","字段:"+fieldCode+",违反校验规则,整数校验范围格式错误!");
				}
				
			}else {
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,整数校验(包括范围)");
			}
			break;
		case "float":
			//浮点数校验(包括范围)
			if(fieldValue.matches("^(-?\\d+)(\\.\\d+)?$")){
				Map<String,Integer> floatvalue = (Map<String, Integer>)JSON.parse(validateJson);
				try {
					boolean result = true;
					Float minvalue = Float.valueOf(floatvalue.get("minvalue"));
					Float maxvalue = Float.valueOf(floatvalue.get("maxvalue"));
					Integer length = floatvalue.get("length");
					if(minvalue != null && !minvalue.equals("")){
						if(Float.parseFloat(fieldValue) < minvalue){
							maps.put("Ispass","false");
							maps.put("Message","字段:"+fieldCode+",违反校验规则,浮点数校验,范围小于最小值!");
							result = false;
						}
					}
					if(maxvalue != null && !maxvalue.equals("")){
						if(Float.parseFloat(fieldValue) > maxvalue){
							maps.put("Ispass","false");
							maps.put("Message","字段:"+fieldCode+",违反校验规则,浮点数校验,范围大于最大值!");
							result = false;
						}
					}
					if(length !=null && !length.equals("")){
						Integer valueLength = fieldValue.substring(fieldValue.indexOf(".")+1).length();
						if(!valueLength.equals(length)){
							maps.put("Ispass","false");
							maps.put("Message","字段:"+fieldCode+",违反校验规则,浮点数校验,小数点位数不匹配!");
							result = false;
						}
					}
					if(result){
						maps.put("Ispass", "true");
					}
				} catch (Exception e) {
					maps.put("Ispass","false");
					maps.put("Message","字段:"+fieldCode+",违反校验规则,浮点数校验格式错误!");
				}
			}else {
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,浮点数校验(包括范围)");
			}
			break;
		case "letter":
			//字母校验(包括大小写)
			if(fieldValue.matches("[a-zA-Z]+")){
				boolean result = true;
				Map<String,Boolean> lettervalue = (Map<String, Boolean>)JSON.parse(validateJson);
				String lowercase = String.valueOf(lettervalue.get("lowercase"));
				String uppercase = String.valueOf(lettervalue.get("uppercase"));
				try {
					if(lowercase != null && !lowercase.equals("")){
						if(lowercase.equals("true")){
							if(!fieldValue.equals(fieldValue.toLowerCase())){
								result = false;
								maps.put("Ispass","false");
								maps.put("Message","字段:"+fieldCode+",违反校验规则,字母小写校验!");
							}
						}
					}
					if(uppercase != null && !uppercase.equals("")){
						if(uppercase.equals("uppercase")){
							if(!fieldValue.equals(fieldValue.toUpperCase())){
								result = false;
								maps.put("Ispass","false");
								maps.put("Message",maps.get("Message")+";字段:"+fieldCode+",违反校验规则,字母大写校验!");
							}
						}
					}
					if(result){
						maps.put("Ispass","true");
					}
				} catch (Exception e) {
					maps.put("Ispass","false");
					maps.put("Message","字段:"+fieldCode+",违反校验规则,字母校验格式错误!");
				}
			}else{
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,字母校验(包括大小写)");
			}
			break;
		case "string":
			//非数字校验
			//^\\d+$
			//\d+(.\d+)
			if(!fieldValue.matches("^\\d+$")){
				maps.put("Ispass", "true");
			}else{
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,非数字校验");
			}
			break;
		case "datetime":
			//日期格式校验
			if(fieldValue != null && !fieldValue.equals("")){
				try {
					DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd"); 
					format1.parse(fieldValue);
					maps.put("Ispass", "true");
				} catch (Exception e) {
					maps.put("Ispass","false");
					maps.put("Message","字段:"+fieldCode+",违反校验规则,日期格式校验");
				}
			}else{
				maps.put("Ispass","false");
				maps.put("Message","字段:"+fieldCode+",违反校验规则,日期格式校验");
			}
			
			break;
		case "no":
			//无校验规则
			maps.put("Ispass", "true");
			break;
		default:
			maps.put("Ispass", "true");
			break;
		}
		return maps;
	}

	/**
	 * show 特殊处理执行MSSql数据库中增、删、改等sql语句
	 * by date 2018/02/23
	 * @param cfgDb  数据源实体
	 * @param strsql 需执行的sql
	 * @return 执行结果
	 * @author guoyanfei
	 */
	@Override
	public Integer executeMSSql(CfgDb cfgDb, String strsql) {
		Integer result = 0;
		Connection conn = null;
		Statement statement = null;
		try{
			//1、加载数据库驱动
			Class.forName(cfgDb.getDbDrivername());
			//2、获取数据库连接
			conn = DriverManager.getConnection(cfgDb.getDbUrl(),
					cfgDb.getDbUser(),cfgDb.getDbPwd());
			//3、获取数据库操作对象
			statement = conn.createStatement();
			//4、执行sql语句(增、删、改)
			result = statement.executeUpdate(strsql);

		}catch (Exception e){
			e.printStackTrace();
		}finally {
			//关闭数据库操作对象
			if(statement != null){
				try{
					statement.close();
				}catch (Exception e){
					e.printStackTrace();
				}
			}

			//关闭数据库连接对象
			if(conn != null){
				try{
					if(!conn.isClosed()){
						conn.close();
					}
				}catch (Exception e){
					e.printStackTrace();
				}
			}
		}

		return result;
	}

	/**
	 * 特殊处理通过jdbc的方式查询记录条数
	 * created by guoyanfei on 2018/04/04
	 * @param cfgDb 数据源实体
	 * @param strsql 需执行的sql
	 * @return 记录条数
	 */
	private Integer selectMSSqlCount(CfgDb cfgDb,String strsql){
		Integer result = 0;
		Connection conn = null;
		Statement statement = null;
		try{
			//1、加载数据库驱动
			Class.forName(cfgDb.getDbDrivername());
			//2、获取数据库连接
			conn = DriverManager.getConnection(cfgDb.getDbUrl(),
					cfgDb.getDbUser(),cfgDb.getDbPwd());
			//3、获取数据库操作对象
			statement = conn.createStatement();
			//4、执行sql语句(查询)
			ResultSet rs = statement.executeQuery(strsql);
			rs.next();
			result=rs.getInt(1);

		}catch (Exception e){
			e.printStackTrace();
		}finally {
			//关闭数据库操作对象
			if(statement != null){
				try{
					statement.close();
				}catch (Exception e){
					e.printStackTrace();
				}
			}

			//关闭数据库连接对象
			if(conn != null){
				try{
					if(!conn.isClosed()){
						conn.close();
					}
				}catch (Exception e){
					e.printStackTrace();
				}
			}
		}

		return result;
	}

	@Override
	public List<Map<String, String>> queryReportQueryParams(CfgDb cfgdb, String serviceID) {
		List<Map<String,String>> result = new ArrayList<>();
		List<BaseRepparmeter> list = baseRepparmeterService.getReportParmByServerByType(serviceID,"dataserverparmeter");
		if(null != list && list.size() > 0) {
			for (BaseRepparmeter br : list) {
				BaseQueryfieldExt queryfield = baseQueryfieldService.getQueryFieldByParamCode(br.getDataservercode(),br.getParmetername());
				Map<String, String> map = new LinkedHashMap<String,String>();
				if(null!=br.getAliasname() && !"".equals(br.getAliasname())) {
					map.put("fieldname", br.getAliasname());
				} else {
					map.put("fieldname", br.getParmetername());
				}
				map.put("fieldnamecn",queryfield.getFieldnameCn());
				map.put("fieldtype",queryfield.getFieldtype());
				map.put("showtype",queryfield.getShowtype().toString());
				map.put("enumvalue",queryfield.getEnumvalue());
				result.add(map);
			}
		}
		List<BaseRepparmeter> replist = baseRepparmeterService.getReportParmByServerByType(serviceID,"templateparmeter");
		if(null != replist && replist.size() > 0 ) {
			for (BaseRepparmeter rep : replist) {
				Map<String, String> map = new LinkedHashMap<String,String>();
				if(null!=rep.getAliasname() && !"".equals(rep.getAliasname())) {
					map.put("fieldname", rep.getAliasname());
				} else {
					map.put("fieldname", rep.getParmetername());
				}
				map.put("fieldnamecn",rep.getParmeterdesc());
				map.put("fieldtype",rep.getParmetertype());
				map.put("showtype","0");
				result.add(map);
			}
		}
		return result;
	}

}
